﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;  
using System.Data;
using System.Windows.Forms;


namespace NNLInterface
{
     
    class ExcelImport
    {
        /// <summary>   
        /// 读取Excel文档   
        /// </summary>   
        /// <param name="Path">文件名称</param>   
        /// <returns>返回一个数据集</returns>   
        public static DataTable ExcelToDS(string excelFile)
        {
            try
            {
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                //此处需要加上HDR=yes和IMEX=1指定第一行为表头
                // 1、HDR表示要把第一行作为数据还是作为列名，作为数据用HDR=no，作为列名用HDR=yes； 
                // 2、通过IMEX=1来把混合型作为文本型读取，避免null值。
                ////string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                //解析路径
                string fileNmae = System.IO.Path.GetFileName(excelFile);      //返回带扩展名的文件名 
                //System.IO.Path.GetFileNameWithoutExtension(excelFile)     //返回不带扩展名的文件名 
                string strPath = System.IO.Path.GetDirectoryName(excelFile);    //返回文件所在目录 

                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Text;";

                //string strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";" + "Extended Properties=Text;HDR=No;FMT=Delimited;Persist Security Info=False";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                //strExcel = "select * from [sheet1$]";
                strExcel = "select * from  " + fileNmae;
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
                DataTable dt = ds.Tables[0];
                return ds.Tables[0];


                //string strconn = @"driver={microsoft text driver (*.txt; *.csv)};dbq=";
                //strconn += System.IO.Path.GetDirectoryName(excelFile);
                //strconn += ";extensions=csv;";
                //using (OdbcConnection objconn = new OdbcConnection(strconn))
                //{

                //    DataSet dscsv = new DataSet();
                //    string strsql = "select * from " + System.IO.Path.GetFileName(excelFile);
                //    OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter(strsql, objconn);
                //    odbcDataAdapter.Fill(dscsv, "dt1");
                //    DataTable dt = dscsv.Tables[0];
                //    return dt;

                //}
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message + "\n文件名:" + excelFile, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
          
        }

    }
}
